package org.kjk.employee;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.kjk.oracleDB.ConnectionPool;

public class EmployeeDAOImpl implements EmployeeDAO {
	ConnectionPool connectionPool;
	Connection conn;

	public EmployeeDAOImpl() {
		try {
			connectionPool = ConnectionPool.getInstance();
			conn = connectionPool.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public <T> int insert(T obj) {
		PreparedStatement pstmt = null;
		String sql = "insert into employee(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)"
				+ "values(?, ?, ?, ?, ?, to_date(?, 'YYYYMMDD'), ?, ?, ?, ?, ?)";
		int res = 0;
		try {
			EmployeeDTO dto = (EmployeeDTO) obj;
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, dto.getEmployeeId());
			pstmt.setString(2, dto.getFirstName());
			pstmt.setString(3, dto.getLastName());
			pstmt.setString(4, dto.geteMail());
			pstmt.setString(5, dto.getPhoneNumber());
			pstmt.setString(6, dto.getHireDate());
			pstmt.setString(7, dto.getJobId());
			pstmt.setInt(8, dto.getSalary());
			pstmt.setInt(9, dto.getCommission());
			pstmt.setInt(10, dto.getManagerId());
			pstmt.setInt(11, dto.getDepartmentId());
			res = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		connectionPool.close(null, pstmt, null);
		return res;
	}

	@Override
	public <T> int update(T obj) {
		PreparedStatement pstmt = null;
		String sql = "update employee set EMPLOYEE_ID=?, FIRST_NAME=?, LAST_NAME=?, EMAIL=?, PHONE_NUMBER=?, HIRE_DATE=?, JOB_ID=?, SALARY=?,"
				+ "COMMISSION_PCT=?, MANAGER_ID=?, DEPARTMENT_ID=? where EMPLOYEE_ID=?";
		int res = 0;
		try {
			pstmt = conn.prepareStatement(sql);
			EmployeeDTO dto = (EmployeeDTO) obj;
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, dto.getEmployeeId());
			pstmt.setString(2, dto.getFirstName());
			pstmt.setString(3, dto.getLastName());
			pstmt.setString(4, dto.geteMail());
			pstmt.setString(5, dto.getPhoneNumber());
			pstmt.setString(6, dto.getHireDate());
			pstmt.setString(7, dto.getJobId());
			pstmt.setInt(8, dto.getSalary());
			pstmt.setInt(9, dto.getCommission());
			pstmt.setInt(10, dto.getManagerId());
			pstmt.setInt(11, dto.getDepartmentId());
			pstmt.setInt(12, dto.getEmployeeId());
			res = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		connectionPool.close(null, pstmt, null);
		return res;
	}

	@Override
	public <T> int delete(T obj) {
		PreparedStatement pstmt = null;
		String sql = "delete from employee where EMPLOYEE_ID=?";
		int res = 0;
		try {
			pstmt = conn.prepareStatement(sql);
			EmployeeDTO dto = (EmployeeDTO) obj;
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, dto.getEmployeeId());
			res = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		connectionPool.close(null, pstmt, null);
		return res;
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public List select(String sql, String[] param) {
		List<EmployeeDTO> result = new ArrayList<EmployeeDTO>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql);
			if (param != null) {
				for (int i = 0; i < param.length; i++) {
					pstmt.setString(i + 1, param[i]);
				}
			}
			rs = pstmt.executeQuery();
			while (rs.next()) {
				int employeeId = rs.getInt("EMPLOYEE_ID");
				String firstName = rs.getString("FIRST_NAME");
				String lastName = rs.getString("LAST_NAME");
				String eMail = rs.getString("EMAIL");
				String phoneNumber = rs.getString("PHONE_NUMBER");
				String hireDate = rs.getString("HIRE_DATE");
				String jobId = rs.getString("JOB_ID");
				int salary = rs.getInt("SALARY");
				int commission = rs.getInt("COMMISSION_PCT");
				int managerId = rs.getInt("MANAGER_ID");
				int departmentId = rs.getInt("DEPARTMENT_ID");
				EmployeeDTO bean = new EmployeeDTO(employeeId, firstName,
						lastName, eMail, phoneNumber, hireDate, jobId, salary,
						commission, managerId, departmentId);
				result.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		connectionPool.close(rs, pstmt, conn);
		return result;
	}

	@Override
	public JSONObject selectJson(String sql, String[] param) throws JSONException {
		JSONObject json = new JSONObject();
		JSONArray array = new JSONArray();
		json.put("result", array);
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql);
			if (param != null) {
				for (int i = 0; i < param.length; i++) {
					pstmt.setString(i + 1, param[i]);
				}
			}
			rs = pstmt.executeQuery();
			while (rs.next()) {
				int employeeId = rs.getInt("EMPLOYEE_ID");
				String firstName = rs.getString("FIRST_NAME");
				String lastName = rs.getString("LAST_NAME");
				String eMail = rs.getString("EMAIL");
				String phoneNumber = rs.getString("PHONE_NUMBER");
				String hireDate = rs.getString("HIRE_DATE");
				String jobId = rs.getString("JOB_ID");
				int salary = rs.getInt("SALARY");
				int commission = rs.getInt("COMMISSION_PCT");
				int managerId = rs.getInt("MANAGER_ID");
				int departmentId = rs.getInt("DEPARTMENT_ID");
				JSONObject obj1 = new JSONObject();
				obj1.put("employeeId", employeeId);
				obj1.put("firstName", firstName);
				obj1.put("lastName", lastName);
				obj1.put("eMail", eMail);
				obj1.put("phoneNumber", phoneNumber);
				obj1.put("hireDate", hireDate);
				obj1.put("jobId", jobId);
				obj1.put("salary", salary);
				obj1.put("commission", commission);
				obj1.put("managerId", managerId);
				obj1.put("departmentId", departmentId);
				array.put(obj1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		connectionPool.close(rs, pstmt, conn);
		return json;
	}
	
	@Override
	protected void finalize() throws Throwable {
		super.finalize();
		connectionPool.close(null, null, conn);
	}

}
